﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace poleOperators
{
    public partial class Form1 : Form
    {
        string strcon = "server=127.0.0.1;user=sa;pwd=krgg;database=Pole";
        public Form1()
        {
            InitializeComponent();
            UpdateBox();
        }

        /// <summary>
        /// 增加数据
        /// </summary>
        /// <param name="sender"></param> 
        /// <param name="e"></param>
        private void add_Click(object sender, EventArgs e)
        {
            string str = "insert into Pole.dbo.PoleModel(id,polemodel) values(" +idBox.Text.TrimStart().TrimEnd()+ "," + "'" + modelBox.Text.TrimStart().TrimEnd() + "'" + ")";
            SqlConnection sqlcon = new SqlConnection(strcon);
            try
            {
                sqlcon.Open();
                SqlCommand ctcmd = new SqlCommand(str, sqlcon);
                ctcmd.ExecuteNonQuery();
                MessageBox.Show("数据添加成功");
                UpdateBox();
            }
            catch (Exception)
            {
                MessageBox.Show("输入不正确");
            }
            finally
            {
                sqlcon.Close();
                idBox.Text = "";
                modelBox.Text = "";
            }
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void delete_Click(object sender, EventArgs e)
        {
            string str1 = "delete from Pole.dbo.PoleModel where id=" + idBox.Text.TrimStart().TrimEnd();
            string str2= "delete from Pole.dbo.PoleModel where polemodel=" + modelBox.Text.TrimStart().TrimEnd();
            SqlConnection sqlcon = new SqlConnection(strcon);
            try
            {
                sqlcon.Open();
                if (idBox.Text!="")
                {
                    SqlCommand sqlcmd1 = new SqlCommand(str1, sqlcon);
                    sqlcmd1.ExecuteNonQuery();
                }
                else
                {
                    SqlCommand sqlcmd2 = new SqlCommand(str1, sqlcon);
                    sqlcmd2.ExecuteNonQuery();
                }
                Box.Update();
                MessageBox.Show("删除成功");
                UpdateBox();
            }
            catch (Exception)
            {
                MessageBox.Show("输入不正确");
            }
            finally
            {               
                idBox.Text = "";
                modelBox.Text = "";
                sqlcon.Close();
            }
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void update_Click(object sender, EventArgs e)
        { 
            string str = "update Pole.dbo.PoleModel set polemodel=" + "'" + modelBox.Text.TrimStart().TrimEnd() + "'" + " where id=" + idBox.Text.TrimStart().TrimEnd();
            SqlConnection sqlcon = new SqlConnection(strcon);
            try
            {
                sqlcon.Open();
                SqlCommand sqlcmd = new SqlCommand(str, sqlcon);
                sqlcmd.ExecuteNonQuery();
                MessageBox.Show("修改成功");
                UpdateBox();
            }
            catch (Exception )
            {
                MessageBox.Show("输入不正确");
            }
            finally
            {
                idBox.Text = "";
                modelBox.Text = "";
                sqlcon.Close();
            }
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void query_Click(object sender, EventArgs e)
        {
            string id = idBox.Text.TrimStart().TrimEnd();
            string model = modelBox.Text.TrimStart().TrimEnd();
            string str = null;
            if (id != "")
            {
                str = "where id=" + id;
            }
            else if (model != "")
            {
                str = "where model=" + "'" + model + "'";
            }
            string str1 = "SELECT * FROM Pole.dbo.PoleModel " + str + " order by id";
            SqlConnection sqlcon = new SqlConnection(strcon);
            try
            {
                sqlcon.Open();
                SqlDataAdapter sqlda = new SqlDataAdapter(str1, sqlcon);
                DataSet ds = new DataSet();
                sqlda.Fill(ds, "Pole.dbo.PoleModel");
                string dataStr = null;
                foreach (DataTable dt in ds.Tables)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        foreach (DataColumn dc in dt.Columns)
                        {
                            dataStr += dr[dc];
                            dataStr += "\t";
                        }
                        dataStr += "\r\n";
                    }
                }
                Box.Text = dataStr;
            }
            catch (Exception)
            {

            }
            finally
            {
                sqlcon.Close();
                idBox.Text = "";
                modelBox.Text = "";
            }
        }

        public void UpdateBox()
        {
            string str1 = "SELECT * FROM Pole.dbo.PoleModel order by id";
            SqlConnection sqlcon = new SqlConnection(strcon);
            try
            {
                sqlcon.Open();
                SqlDataAdapter sqlda = new SqlDataAdapter(str1, sqlcon);
                DataSet ds = new DataSet();
                sqlda.Fill(ds, "Pole.dbo.PoleModel");
                string dataStr = null;
                foreach (DataTable dt in ds.Tables)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        foreach (DataColumn dc in dt.Columns)
                        {
                            dataStr += dr[dc];
                            dataStr += "\t";
                        }
                        dataStr += "\r\n";
                    }
                }
                Box.Text = dataStr;
            }
            catch (Exception)
            {

            }
            finally
            {
                sqlcon.Close();
                GC.Collect();
            }
        }
    }    
}
